package com.wiiss.ams.dao;

import cn.hutool.core.util.StrUtil;
import cn.hutool.json.JSONObject;
import cn.hutool.json.JSONUtil;
import cn.hutool.log.StaticLog;
import com.wiiss.ams.util.Const;
import com.wiiss.ams.util.Exc;

import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.Collectors;
import java.util.stream.Stream;

public class OracleDML {

	private static final int BATCH_INSERT_SIZE = 1000;

	public static List<JSONObject> get(String database, String sql, LocalDateTime from, LocalDateTime to) {
		try {
			return DbFactory.get(database).query(sql, Map.of("from", Const.DATE_TIME_FORMATTER.format(from), "to", Const.DATE_TIME_FORMATTER.format(to))).stream()
				.map(JSONObject::new).toList();
		} catch (Exception e) {
			throw new Exc(e, "oracle query failed");
		}
	}

	public static List<JSONObject> getWithNoParam(String database, String sql) {
		try {
			return DbFactory.get(database).query(sql).stream().map(q->JSONUtil.parseObj(q).setDateFormat("yyyy-MM-dd HH:mm:ss")).toList();
		} catch (Exception e) {
			throw new Exc(e, "oracle query failed");
		}
	}

	public static int deleteBefore(String database, String table, String byField, LocalDateTime beforeTime) {
		int res = 0;
		try {
			res = DbFactory.get(database).execute(StrUtil.format("""
					DELETE FROM {} WHERE "{}" < TO_DATE('{}', 'yyyy-mm-dd hh24:mi:ss')
					""", table, byField, Const.DATE_TIME_FORMATTER.format(beforeTime)));
		} catch (Exception e) {
			throw new Exc(e, "delete failed");
		}
		return res;
	}

	public static int truncate(String database, String table) {
		AtomicInteger effectedRow = new AtomicInteger(0);
		try {
			DbFactory.get(database).tx(db -> {
				effectedRow.addAndGet(db.execute(StrUtil.format("""
						BEGIN
						    EXECUTE IMMEDIATE 'TRUNCATE TABLE {}';
						END;
						""", table)));
			});
		} catch (Exception e) {
			throw new Exc(e, "oracle truncate failed");
		}
		return effectedRow.get();
	}

	public static int batchInsert(String database, String table, List<JSONObject> jsonObjects) {
		if (jsonObjects.isEmpty()) {
			return 0;
		}

		// 组装 insert sql
		List<String> fields = jsonObjects.get(0).keySet().stream().toList();
		String subInsertSql = "INTO " + table + fields.stream().map(f -> "\"" + f + "\"").collect(Collectors.joining(",", "(", ")")) + " VALUES "; // 字段加引号,兼容小写
		List<String> insertSqls = segment(jsonObjects, BATCH_INSERT_SIZE).stream()
			.map(list -> list.stream().map(e -> subInsertSql + fields.stream().map(f -> adapt2oracle(e, f)).collect(Collectors.joining(",", "(", ")")))
				.collect(Collectors.joining(" ", "BEGIN INSERT ALL ", " SELECT 1 FROM DUAL; END;")))
			.toList();

		AtomicInteger counter = new AtomicInteger(0);

		// insert
		try {
			DbFactory.get(database).tx(db -> {
				for (String sql : insertSqls) {
					counter.addAndGet(db.execute(sql));
				}
			});
		} catch (Exception e) {
			throw new Exc(e, "batch insert failed, sql: {}", insertSqls);
		}

		return counter.get();
	}

	/**
	 * 先去重再插入
	 * @param database        数据库
	 * @param table           表
	 * @param indexedField    索引字段 用来判断数据的唯一性
	 * @param sortedTimeField 时间排序字段
	 * @param afterTime       时间条件, 不必每次查全量数据
	 * @param dataList        待插入数据(有重复)
	 * @author Vec 2024年4月3日 08:43:23
	 */
	public static Integer distinctAndBatchInsert(String database, String table, String indexedField, String sortedTimeField, LocalDateTime afterTime, List<JSONObject> dataList) {
		if (dataList.isEmpty()) {
			return 0;
		}

		List<String> existedIndexes = new ArrayList<>();
		try {
			existedIndexes.addAll(DbFactory.get(database).query(StrUtil.format("""
					SELECT
					    "{}"
					FROM
					    "{}"
					WHERE
					    "{}" >= TO_DATE('{}', 'yyyy-mm-dd hh24:mi:ss')
					""", indexedField, table, sortedTimeField, Const.DATE_TIME_FORMATTER.format(afterTime)), String.class));
		} catch (Exception e) {
			StaticLog.error(e);
			throw new RuntimeException("oracle queries failed", e);
		}

		return batchInsert(database, table, dataList.stream().filter(v -> !existedIndexes.contains(v.getStr(indexedField))).toList());
	}

	/**
	 * 将数据分段
	 * @param sources  数据源
	 * @param eachSize 每段大小
	 * @return 分段后的新集合
	 * @author 52291590 2022年11月17日 上午10:25:07
	 */
	private static List<List<JSONObject>> segment(List<JSONObject> sources, int eachSize) {
		return Stream.iterate(0, i -> i < sources.size(), i -> i + eachSize)
			.map(i -> sources.size() - i >= eachSize ? sources.subList(i, i + eachSize) : sources.subList(i, sources.size())).toList();
	}

	/**
	 * 适配字段值 以符合oracle要求
	 * @author 52291590 2022年11月18日 上午9:15:41
	 */
	private static String adapt2oracle(JSONObject jo, String field) {
		// 排除null 只适配了字符类型
		if (jo.getStr(field) == null) {
			return "''";
		}

		// 特殊处理下时间 不然oracle不给插
		return field.toLowerCase().contains("time") ? String.format("TO_DATE('%s', 'yyyy-mm-dd hh24:mi:ss')", formatTime(jo.getStr(field))) : "'" + jo.getStr(field) + "'";
	}

	/**
	 * 解决毫秒问题 e.g "2022-11-22 16:45:40.0"
	 * @author 52291590 2022年11月22日 下午5:04:38
	 */
	private static String formatTime(String timeStr) {
		int i = timeStr.indexOf(".");
		return i == -1 ? timeStr : timeStr.substring(0, i);
	}

}
